#!/usr/bin/env perl
# License: GNU GPL 2
# Author: Muhammad Ichsan <ichsan@gmail.com>

$db = "qr";
$db_user = "qr_usr";
$db_pwd = "qr_pwd";

sub prepare_tables {
  local($lang, $translator, $copyright, $sqlfile);
  $lang = shift;
  $translator = shift;
  $copyright = shift;
  $sqlfile = shift;

  $sql = "
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;
SET default_tablespace = '';

SET default_with_oids = false;

DROP TABLE IF EXISTS quran_chapters_$lang;
CREATE TABLE quran_chapters_$lang (
  number int DEFAULT 0 NOT NULL,
  title character varying(100) NOT NULL,
      meaning character varying(100)
);

DROP TABLE IF EXISTS quran_verses_$lang;
CREATE TABLE quran_verses_$lang (
  id integer NOT NULL,
  chapter_number int DEFAULT 0 NOT NULL,
  number int DEFAULT 0 NOT NULL,
  content text NOT NULL
);

COMMENT ON TABLE quran_verses_$lang IS 'by:$translator, c:$copyright';

DROP SEQUENCE IF EXISTS quran_verses_$lang_id_seq;
CREATE SEQUENCE quran_verses_". $lang ."_id_seq
  START WITH 1
  INCREMENT BY 1
  NO MAXVALUE
  NO MINVALUE
  CACHE 1;
  ALTER SEQUENCE quran_verses_". $lang ."_id_seq OWNED BY quran_verses_$lang.id;

                                                                                                              SELECT pg_catalog.setval('quran_verses_". $lang ."_id_seq', 1, false);

ALTER TABLE quran_verses_$lang ALTER COLUMN id SET DEFAULT nextval('quran_verses_". $lang ."_id_seq'::regclass);
";

  open(SQL_FILE, ">>$sqlfile");
  print SQL_FILE $sql;
  close(SQL_FILE);

  return 1;
}

sub import_tables {
  local($lang, $file);
  $lang = shift;
  $file = shift;

  system("psql -h localhost -U $db_user $db < $file > /tmp/silent");

  return 1
}

sub export_tables {
  my $lang = shift;

  system("pg_dump -h localhost -c -O -U $db_user $db -t quran_chapters_$lang -t quran_verses_$lang |bzip2 > quran-$lang.pgsql.bz2");

  return 1;
}
